package cn.ccccltd.waf.message.util.sql;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import cn.ccccltd.waf.message.key.Key;
import cn.ccccltd.waf.message.util.NameUtil;

/**
 * 创建日期:2017年11月1日
 * Title:sql辅助类
 * Description：对本文件的详细描述，原则上不能少于50字
 * @author yangjingjiang
 * @mender：（文件的修改者，文件创建者之外的人）
 * @version 1.0
 * Remark：认为有必要的其他信息
 */
public class SqlTools {

	private static final String UID = "serialVersionUID";
	private static final String SELECT = "SELECT * FROM ";
	private static final String SELECTY = "SELECT ";
	private static final String SELECTCOUNT = "SELECT COUNT(*) ";
	private static final String INSERT = "INSERT INTO ";
	private static final String UPDATE = "UPDATE ";
	private static final String SET = " SET ";
	private static final String WHERE = " WHERE ";
	private static final String WHEREOK = " WHERE 1=1";
	private static final String WHERENO = " WHERE 1=2";
	private static final String DELETE = "DELETE FROM ";
	private static final String LEFT = "(";
	private static final String VALUES = ") VALUES (";
	private static final String RIGHT = ")";
	private static final String AND = " AND ";
	private static final String OR = " OR ";
	private static final String LINK = ",";
	private static final String OCCUPY = "?,";
	private static final String EQUAL = " =? ";
	private static final String EQUAL_LINK = "=?,";
	private static final String EQUAL_AND = "=? AND ";
	private static final String FROM = " FROM ";
	private static final String ORDER = "ORDER BY";
	private static final String LIMIT = " LIMIT ";
	private static final String order = "order by";
	private static final String limit = " limit ";
	private static final String from = "from";
	private static final String EMPTY = "";
	private static final String R_ORDER = "ORDER.*";
	private static final String R_LIMIT = "LIMIT.*";

	/**
	 * 功能: 构建insert语句<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月1日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getInsert(Object bean) {
		List<Object> values = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		StringBuilder placeholder = new StringBuilder();
		try {
			Class<?> cls = bean.getClass();
			Field[] declaredFields = getFields(cls);
			for (Field field : declaredFields) {
				field.setAccessible(true);
				Object val = field.get(bean);
				if (val != null) {
					cols.append(NameUtil.getUnderLineName(field.getName())).append(LINK);
					placeholder.append(OCCUPY);
					values.add(val);
				}
			}
			if (cols.length()>1 || placeholder.length() > 1) {
				cols.deleteCharAt(cols.length() - 1);
				placeholder.deleteCharAt(placeholder.length() - 1);
			}
			
			sql.append(INSERT);
			sql.append(NameUtil.getTableName(cls.getSimpleName()));
			sql.append(LEFT);
			sql.append(cols);
			sql.append(VALUES);
			sql.append(placeholder);
			sql.append(RIGHT);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, values.toArray());
	}
	
	/**
	 * 功能: 构建父类的insert语句<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月1日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getSuperInsert(Object bean) {
		List<Object> values = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		StringBuilder placeholder = new StringBuilder();
		try {
			Class<?> clsChild = bean.getClass();
			Class<?> cls = clsChild.getSuperclass();
			Field[] declaredFields = getFields(cls);
			for (Field field : declaredFields) {
				field.setAccessible(true);
				Object val = field.get(bean);
				if (val != null) {
					cols.append(NameUtil.getUnderLineName(field.getName())).append(LINK);
					placeholder.append(OCCUPY);
					values.add(val);
				}
			}
			if (cols.length()>1 || placeholder.length() > 1) {
				cols.deleteCharAt(cols.length() - 1);
				placeholder.deleteCharAt(placeholder.length() - 1);
			}
			
			sql.append(INSERT);
			sql.append(NameUtil.getTableName(cls.getSimpleName()));
			sql.append(LEFT);
			sql.append(cols);
			sql.append(VALUES);
			sql.append(placeholder);
			sql.append(RIGHT);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, values.toArray());
	}

	/**
	 * 功能: 构建所有属性的insert语句<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月21日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getInsertAllProperty(Object bean) {
		String tableName = "";
		List<Object> values = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		StringBuilder placeholder = new StringBuilder();
		try {
			Class<?> cls = bean.getClass();
			tableName = NameUtil.getTableName(cls.getSimpleName());
			while (null != cls) {
				Field[] declaredFields = getFields(cls);
				for (Field field : declaredFields) {
					field.setAccessible(true);
					Object val = field.get(bean);
					if (val != null) {
						cols.append(NameUtil.getUnderLineName(field.getName())).append(LINK);
						placeholder.append(OCCUPY);
						values.add(val);
					}
				}
				cls = cls.getSuperclass();
			}
			
			if (cols.length()>1 || placeholder.length() > 1) {
				cols.deleteCharAt(cols.length() - 1);
				placeholder.deleteCharAt(placeholder.length() - 1);
			}
			
			sql.append(INSERT);
			sql.append(tableName);
			sql.append(LEFT);
			sql.append(cols);
			sql.append(VALUES);
			sql.append(placeholder);
			sql.append(RIGHT);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, values.toArray());
	}
	
	/**
	 * 功能: 构建update语句,实体中必须添加@key注解<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月2日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getUpdate(Object bean) {
		List<Object> values = new ArrayList<Object>();
		List<Object> wheresValue = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		StringBuilder where = new StringBuilder();
		try {
			Class<?> cls = bean.getClass();
			for (Field field : getFields(cls)) {
				field.setAccessible(true);
				Object val = field.get(bean);
				if (val != null) {
					if (field.isAnnotationPresent(Key.class)) {
						where.append(NameUtil.getUnderLineName(field.getName())).append(EQUAL_AND);
						wheresValue.add(val);
					} else {
						cols.append(NameUtil.getUnderLineName(field.getName())).append(EQUAL_LINK);
						values.add(val);
					}
				}
			}
			cols.deleteCharAt(cols.length() - 1);
			where.delete(where.length() - 4, where.length());
			values.addAll(wheresValue);
			sql.append(UPDATE);
			sql.append(NameUtil.getTableName(cls.getSimpleName()));
			sql.append(SET);
			sql.append(cols);
			sql.append(WHERE);
			sql.append(where);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, values.toArray());
	}
	
	/**
	 * 功能: 构建父类的update语句,实体中必须添加@key注解<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月21日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getSuperUpdate(Object bean) {
		List<Object> values = new ArrayList<Object>();
		List<Object> wheresValue = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		StringBuilder where = new StringBuilder();
		try {
			Class<?> clsChild = bean.getClass();
			Class<?> cls = clsChild.getSuperclass();
			for (Field field : getFields(cls)) {
				field.setAccessible(true);
				Object val = field.get(bean);
				if (val != null) {
					if (field.isAnnotationPresent(Key.class)) {
						where.append(NameUtil.getUnderLineName(field.getName())).append(EQUAL_AND);
						wheresValue.add(val);
					} else {
						cols.append(NameUtil.getUnderLineName(field.getName())).append(EQUAL_LINK);
						values.add(val);
					}
				}
			}
			cols.deleteCharAt(cols.length() - 1);
			where.delete(where.length() - 4, where.length());
			values.addAll(wheresValue);
			sql.append(UPDATE);
			sql.append(NameUtil.getTableName(cls.getSimpleName()));
			sql.append(SET);
			sql.append(cols);
			sql.append(WHERE);
			sql.append(where);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, values.toArray());
	}


	/**
	 * 功能: 构建delete语句（删除条件为实体对象@key字段）<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月21日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param bean
	 * @return
	 */
	public static SqlContext getDelete(Object bean) {
		List<Object> wheresValue = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		StringBuilder where = new StringBuilder();
		try {
			Class<?> cls = bean.getClass();
			for (Field field : getFields(cls)) {
				field.setAccessible(true);
				Object val = field.get(bean);
				if (val != null && field.isAnnotationPresent(Key.class)) {
					where.append(field.getName()).append(EQUAL_AND);
					wheresValue.add(val);
				}
			}
			where.delete(where.length() - 4, where.length());
			sql.append(DELETE);
			sql.append(NameUtil.getTableName(cls.getSimpleName()));
			sql.append(WHERE);
			sql.append(where);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return new SqlContext(sql, wheresValue.toArray());
	}

	
	/**
	 * 功能: 根据带有@key的字段查询<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月15日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param cls
	 * @param id
	 * @return
	 */
	public static SqlContext getByKey(Class<?> cls, Object id) {
		
		SqlContext sqlContext = new SqlContext();
		
		String	tableName = NameUtil.getTableName(cls.getSimpleName());
		
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		
		String primary = "";
		
		while (null != cls) {
			Field[] declaredFields = getFields(cls);
			for (Field field : declaredFields) {
				field.setAccessible(true);
				if (field.isAnnotationPresent(Key.class)) {
					//设置查询条件
					primary = field.getName() + EQUAL;
					sqlContext.setParams(new Object[] { id });
				}
				cols.append(NameUtil.getUnderLineName(field.getName())).append(" ").append(LINK);
				
			}
			cls = cls.getSuperclass();
		}
		
		if (cols.length()>1 ) {
			cols.deleteCharAt(cols.length() - 1);
		}
		
		sql.append(SELECTY);
		sql.append(cols);
		sql.append(FROM + tableName).append(WHERE).append(primary);
		sqlContext.setSql(sql);
	
		
		
		return sqlContext;
	}

	/**
	 * 功能: 构建count语句<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月10日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param tableName
	 * @return
	 */
	public static String getCountSql(String tableName) {

		String sql = SELECTCOUNT + FROM + tableName + WHEREOK;
		
		return sql;
	}
	
	/**
	 * 功能: 获取所有的属性<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月21日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param cls
	 * @return
	 */
	private static Field[] getFields(Class<?> cls) {
		
		List<Field> allField = new ArrayList<>();
		
		Field[] declaredFields = cls.getDeclaredFields();
		for (int i = 0; i < declaredFields.length; i++) {
			
			Field field = declaredFields[i];
			
			if (UID.equals(field.getName())) {
				continue;
			}
			
			allField.add(field);
		}
		
		Field[] fields = new Field[allField.size()];
		for (int i = 0; i < allField.size(); i++) {
			fields[i] = allField.get(i);
		}
		
		return fields;
	}
	
	/**
	 * 功能: 获取查询属性<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月21日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @param tableName
	 * @param cls
	 * @param removeField
	 * @return
	 */
	@SafeVarargs
	public static String getSelectAllProperty(String tableName, Class<?> cls,List<String>... removeField) {
		
		if(StringUtils.isBlank(tableName)) {
			tableName = NameUtil.getTableName(cls.getSimpleName());
		}
		
		StringBuilder sql = new StringBuilder();
		StringBuilder cols = new StringBuilder();
		
		while (null != cls) {
			Field[] declaredFields = getFields(cls);
			for (Field field : declaredFields) {
				
				if (removeField.length >0 && removeField[0].contains(field.getName())) {
					continue;
				}
				
				cols.append(NameUtil.getUnderLineName(field.getName())).append(" ").append(LINK);
				
			}
			cls = cls.getSuperclass();
		}
		
		if (cols.length()>1 ) {
			cols.deleteCharAt(cols.length() - 1);
		}
		
		sql.append(SELECTY);
		sql.append(cols);
		sql.append(FROM + tableName + WHEREOK);
		
		return sql.toString();
	}
	
}